
USE [SaleManagement]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_Get_List

AS


				
				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[CustomerId],
					[Date],
					[Total],
					[Commission],
					[RealTotal],
					[Notes],
					[CreatedDate],
					[CreatedBy],
					[UpdatedDate],
					[UpdatedBy],
					[RequiredModified],
					[Status]
				FROM
					[dbo].[tblPurchase]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblPurchase table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblPurchase]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[RepositoryId], O.[RefNo], O.[CustomerId], O.[Date], O.[Total], O.[Commission], O.[RealTotal], O.[Notes], O.[CreatedDate], O.[CreatedBy], O.[UpdatedDate], O.[UpdatedBy], O.[RequiredModified], O.[Status]
				FROM
				    [dbo].[tblPurchase] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblPurchase]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_Insert
(

	@Id int    OUTPUT,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@CustomerId int   ,

	@Date datetime   ,

	@Total int   ,

	@Commission int   ,

	@RealTotal int   ,

	@Notes nvarchar (1024)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@RequiredModified ntext   ,

	@Status char (1)  
)
AS


				
				INSERT INTO [dbo].[tblPurchase]
					(
					[RepositoryId]
					,[RefNo]
					,[CustomerId]
					,[Date]
					,[Total]
					,[Commission]
					,[RealTotal]
					,[Notes]
					,[CreatedDate]
					,[CreatedBy]
					,[UpdatedDate]
					,[UpdatedBy]
					,[RequiredModified]
					,[Status]
					)
				VALUES
					(
					@RepositoryId
					,@RefNo
					,@CustomerId
					,@Date
					,@Total
					,@Commission
					,@RealTotal
					,@Notes
					,@CreatedDate
					,@CreatedBy
					,@UpdatedDate
					,@UpdatedBy
					,@RequiredModified
					,@Status
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_Update
(

	@Id int   ,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@CustomerId int   ,

	@Date datetime   ,

	@Total int   ,

	@Commission int   ,

	@RealTotal int   ,

	@Notes nvarchar (1024)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@RequiredModified ntext   ,

	@Status char (1)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblPurchase]
				SET
					[RepositoryId] = @RepositoryId
					,[RefNo] = @RefNo
					,[CustomerId] = @CustomerId
					,[Date] = @Date
					,[Total] = @Total
					,[Commission] = @Commission
					,[RealTotal] = @RealTotal
					,[Notes] = @Notes
					,[CreatedDate] = @CreatedDate
					,[CreatedBy] = @CreatedBy
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
					,[RequiredModified] = @RequiredModified
					,[Status] = @Status
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblPurchase] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_GetByCustomerId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_GetByCustomerId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_GetByCustomerId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPurchase table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_GetByCustomerId
(

	@CustomerId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[CustomerId],
					[Date],
					[Total],
					[Commission],
					[RealTotal],
					[Notes],
					[CreatedDate],
					[CreatedBy],
					[UpdatedDate],
					[UpdatedBy],
					[RequiredModified],
					[Status]
				FROM
					[dbo].[tblPurchase]
				WHERE
					[CustomerId] = @CustomerId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPurchase table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[CustomerId],
					[Date],
					[Total],
					[Commission],
					[RealTotal],
					[Notes],
					[CreatedDate],
					[CreatedBy],
					[UpdatedDate],
					[UpdatedBy],
					[RequiredModified],
					[Status]
				FROM
					[dbo].[tblPurchase]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_GetByRefNo procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_GetByRefNo') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_GetByRefNo
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPurchase table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_GetByRefNo
(

	@RefNo varchar (16)  
)
AS


				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[CustomerId],
					[Date],
					[Total],
					[Commission],
					[RealTotal],
					[Notes],
					[CreatedDate],
					[CreatedBy],
					[UpdatedDate],
					[UpdatedBy],
					[RequiredModified],
					[Status]
				FROM
					[dbo].[tblPurchase]
				WHERE
					[RefNo] = @RefNo
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchase_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchase_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchase_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblPurchase table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchase_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@RepositoryId int   = null ,

	@RefNo varchar (16)  = null ,

	@CustomerId int   = null ,

	@Date datetime   = null ,

	@Total int   = null ,

	@Commission int   = null ,

	@RealTotal int   = null ,

	@Notes nvarchar (1024)  = null ,

	@CreatedDate datetime   = null ,

	@CreatedBy varchar (50)  = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null ,

	@RequiredModified ntext   = null ,

	@Status char (1)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [RefNo]
	, [CustomerId]
	, [Date]
	, [Total]
	, [Commission]
	, [RealTotal]
	, [Notes]
	, [CreatedDate]
	, [CreatedBy]
	, [UpdatedDate]
	, [UpdatedBy]
	, [RequiredModified]
	, [Status]
    FROM
	[dbo].[tblPurchase]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([RepositoryId] = @RepositoryId OR @RepositoryId IS NULL)
	AND ([RefNo] = @RefNo OR @RefNo IS NULL)
	AND ([CustomerId] = @CustomerId OR @CustomerId IS NULL)
	AND ([Date] = @Date OR @Date IS NULL)
	AND ([Total] = @Total OR @Total IS NULL)
	AND ([Commission] = @Commission OR @Commission IS NULL)
	AND ([RealTotal] = @RealTotal OR @RealTotal IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
	AND ([Status] = @Status OR @Status IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [RefNo]
	, [CustomerId]
	, [Date]
	, [Total]
	, [Commission]
	, [RealTotal]
	, [Notes]
	, [CreatedDate]
	, [CreatedBy]
	, [UpdatedDate]
	, [UpdatedBy]
	, [RequiredModified]
	, [Status]
    FROM
	[dbo].[tblPurchase]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([RepositoryId] = @RepositoryId AND @RepositoryId is not null)
	OR ([RefNo] = @RefNo AND @RefNo is not null)
	OR ([CustomerId] = @CustomerId AND @CustomerId is not null)
	OR ([Date] = @Date AND @Date is not null)
	OR ([Total] = @Total AND @Total is not null)
	OR ([Commission] = @Commission AND @Commission is not null)
	OR ([RealTotal] = @RealTotal AND @RealTotal is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([CreatedBy] = @CreatedBy AND @CreatedBy is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	OR ([Status] = @Status AND @Status is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblPrice table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_Get_List

AS


				
				SELECT
					[Id],
					[ProductId],
					[EffectiveDate],
					[Price],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblPrice]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblPrice table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblPrice]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[ProductId], O.[EffectiveDate], O.[Price], O.[UpdatedBy], O.[UpdatedDate]
				FROM
				    [dbo].[tblPrice] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblPrice]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPrice table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_Insert
(

	@Id int    OUTPUT,

	@ProductId int   ,

	@EffectiveDate datetime   ,

	@Price int   ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS


				
				INSERT INTO [dbo].[tblPrice]
					(
					[ProductId]
					,[EffectiveDate]
					,[Price]
					,[UpdatedBy]
					,[UpdatedDate]
					)
				VALUES
					(
					@ProductId
					,@EffectiveDate
					,@Price
					,@UpdatedBy
					,@UpdatedDate
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPrice table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_Update
(

	@Id int   ,

	@ProductId int   ,

	@EffectiveDate datetime   ,

	@Price int   ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblPrice]
				SET
					[ProductId] = @ProductId
					,[EffectiveDate] = @EffectiveDate
					,[Price] = @Price
					,[UpdatedBy] = @UpdatedBy
					,[UpdatedDate] = @UpdatedDate
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPrice table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblPrice] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_GetByProductId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_GetByProductId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_GetByProductId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPrice table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_GetByProductId
(

	@ProductId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[ProductId],
					[EffectiveDate],
					[Price],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblPrice]
				WHERE
					[ProductId] = @ProductId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPrice table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[ProductId],
					[EffectiveDate],
					[Price],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblPrice]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPrice_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPrice_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPrice_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblPrice table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPrice_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ProductId int   = null ,

	@EffectiveDate datetime   = null ,

	@Price int   = null ,

	@UpdatedBy varchar (50)  = null ,

	@UpdatedDate datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [ProductId]
	, [EffectiveDate]
	, [Price]
	, [UpdatedBy]
	, [UpdatedDate]
    FROM
	[dbo].[tblPrice]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([EffectiveDate] = @EffectiveDate OR @EffectiveDate IS NULL)
	AND ([Price] = @Price OR @Price IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [ProductId]
	, [EffectiveDate]
	, [Price]
	, [UpdatedBy]
	, [UpdatedDate]
    FROM
	[dbo].[tblPrice]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([EffectiveDate] = @EffectiveDate AND @EffectiveDate is not null)
	OR ([Price] = @Price AND @Price is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblPreviousPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_Get_List

AS


				
				SELECT
					[Id],
					[Code],
					[ProductId],
					[PurchaseDate],
					[CustomerId],
					[Note],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblPreviousPurchase]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblPreviousPurchase table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblPreviousPurchase]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Code], O.[ProductId], O.[PurchaseDate], O.[CustomerId], O.[Note], O.[CreatedDate], O.[CreatedBy]
				FROM
				    [dbo].[tblPreviousPurchase] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblPreviousPurchase]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPreviousPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_Insert
(

	@Id int    OUTPUT,

	@Code varchar (16)  ,

	@ProductId int   ,

	@PurchaseDate datetime   ,

	@CustomerId int   ,

	@Note nvarchar (256)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblPreviousPurchase]
					(
					[Code]
					,[ProductId]
					,[PurchaseDate]
					,[CustomerId]
					,[Note]
					,[CreatedDate]
					,[CreatedBy]
					)
				VALUES
					(
					@Code
					,@ProductId
					,@PurchaseDate
					,@CustomerId
					,@Note
					,@CreatedDate
					,@CreatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPreviousPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_Update
(

	@Id int   ,

	@Code varchar (16)  ,

	@ProductId int   ,

	@PurchaseDate datetime   ,

	@CustomerId int   ,

	@Note nvarchar (256)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblPreviousPurchase]
				SET
					[Code] = @Code
					,[ProductId] = @ProductId
					,[PurchaseDate] = @PurchaseDate
					,[CustomerId] = @CustomerId
					,[Note] = @Note
					,[CreatedDate] = @CreatedDate
					,[CreatedBy] = @CreatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPreviousPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblPreviousPurchase] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_GetByProductId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_GetByProductId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_GetByProductId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPreviousPurchase table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_GetByProductId
(

	@ProductId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[ProductId],
					[PurchaseDate],
					[CustomerId],
					[Note],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblPreviousPurchase]
				WHERE
					[ProductId] = @ProductId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPreviousPurchase table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Code],
					[ProductId],
					[PurchaseDate],
					[CustomerId],
					[Note],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblPreviousPurchase]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPreviousPurchase_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPreviousPurchase_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPreviousPurchase_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblPreviousPurchase table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPreviousPurchase_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Code varchar (16)  = null ,

	@ProductId int   = null ,

	@PurchaseDate datetime   = null ,

	@CustomerId int   = null ,

	@Note nvarchar (256)  = null ,

	@CreatedDate datetime   = null ,

	@CreatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [ProductId]
	, [PurchaseDate]
	, [CustomerId]
	, [Note]
	, [CreatedDate]
	, [CreatedBy]
    FROM
	[dbo].[tblPreviousPurchase]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Code] = @Code OR @Code IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([PurchaseDate] = @PurchaseDate OR @PurchaseDate IS NULL)
	AND ([CustomerId] = @CustomerId OR @CustomerId IS NULL)
	AND ([Note] = @Note OR @Note IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [ProductId]
	, [PurchaseDate]
	, [CustomerId]
	, [Note]
	, [CreatedDate]
	, [CreatedBy]
    FROM
	[dbo].[tblPreviousPurchase]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Code] = @Code AND @Code is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([PurchaseDate] = @PurchaseDate AND @PurchaseDate is not null)
	OR ([CustomerId] = @CustomerId AND @CustomerId is not null)
	OR ([Note] = @Note AND @Note is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([CreatedBy] = @CreatedBy AND @CreatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_Get_List

AS


				
				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[ImportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblImport]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblImport table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblImport]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[RepositoryId], O.[RefNo], O.[ImportDate], O.[TotalAmount], O.[Notes], O.[UpdatedDate], O.[UpdatedBy]
				FROM
				    [dbo].[tblImport] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblImport]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_Insert
(

	@Id int    OUTPUT,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@ImportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblImport]
					(
					[RepositoryId]
					,[RefNo]
					,[ImportDate]
					,[TotalAmount]
					,[Notes]
					,[UpdatedDate]
					,[UpdatedBy]
					)
				VALUES
					(
					@RepositoryId
					,@RefNo
					,@ImportDate
					,@TotalAmount
					,@Notes
					,@UpdatedDate
					,@UpdatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_Update
(

	@Id int   ,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@ImportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblImport]
				SET
					[RepositoryId] = @RepositoryId
					,[RefNo] = @RefNo
					,[ImportDate] = @ImportDate
					,[TotalAmount] = @TotalAmount
					,[Notes] = @Notes
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblImport] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_GetByRepositoryId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_GetByRepositoryId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_GetByRepositoryId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblImport table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_GetByRepositoryId
(

	@RepositoryId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[ImportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblImport]
				WHERE
					[RepositoryId] = @RepositoryId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblImport table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[ImportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblImport]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImport_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImport_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImport_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblImport table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImport_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@RepositoryId int   = null ,

	@RefNo varchar (16)  = null ,

	@ImportDate datetime   = null ,

	@TotalAmount int   = null ,

	@Notes nvarchar (1024)  = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [RefNo]
	, [ImportDate]
	, [TotalAmount]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblImport]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([RepositoryId] = @RepositoryId OR @RepositoryId IS NULL)
	AND ([RefNo] = @RefNo OR @RefNo IS NULL)
	AND ([ImportDate] = @ImportDate OR @ImportDate IS NULL)
	AND ([TotalAmount] = @TotalAmount OR @TotalAmount IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [RefNo]
	, [ImportDate]
	, [TotalAmount]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblImport]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([RepositoryId] = @RepositoryId AND @RepositoryId is not null)
	OR ([RefNo] = @RefNo AND @RefNo is not null)
	OR ([ImportDate] = @ImportDate AND @ImportDate is not null)
	OR ([TotalAmount] = @TotalAmount AND @TotalAmount is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblAccessoryImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_Get_List

AS


				
				SELECT
					[Id],
					[RefNo],
					[ImportDate],
					[TotalAmount],
					[Notes],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblAccessoryImport]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblAccessoryImport table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblAccessoryImport]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[RefNo], O.[ImportDate], O.[TotalAmount], O.[Notes], O.[UpdatedBy], O.[UpdatedDate]
				FROM
				    [dbo].[tblAccessoryImport] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblAccessoryImport]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblAccessoryImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_Insert
(

	@Id int    OUTPUT,

	@RefNo varchar (16)  ,

	@ImportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS


				
				INSERT INTO [dbo].[tblAccessoryImport]
					(
					[RefNo]
					,[ImportDate]
					,[TotalAmount]
					,[Notes]
					,[UpdatedBy]
					,[UpdatedDate]
					)
				VALUES
					(
					@RefNo
					,@ImportDate
					,@TotalAmount
					,@Notes
					,@UpdatedBy
					,@UpdatedDate
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblAccessoryImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_Update
(

	@Id int   ,

	@RefNo varchar (16)  ,

	@ImportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblAccessoryImport]
				SET
					[RefNo] = @RefNo
					,[ImportDate] = @ImportDate
					,[TotalAmount] = @TotalAmount
					,[Notes] = @Notes
					,[UpdatedBy] = @UpdatedBy
					,[UpdatedDate] = @UpdatedDate
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblAccessoryImport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblAccessoryImport] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblAccessoryImport table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[RefNo],
					[ImportDate],
					[TotalAmount],
					[Notes],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblAccessoryImport]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccessoryImport_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccessoryImport_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccessoryImport_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblAccessoryImport table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccessoryImport_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@RefNo varchar (16)  = null ,

	@ImportDate datetime   = null ,

	@TotalAmount int   = null ,

	@Notes nvarchar (1024)  = null ,

	@UpdatedBy varchar (50)  = null ,

	@UpdatedDate datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [RefNo]
	, [ImportDate]
	, [TotalAmount]
	, [Notes]
	, [UpdatedBy]
	, [UpdatedDate]
    FROM
	[dbo].[tblAccessoryImport]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([RefNo] = @RefNo OR @RefNo IS NULL)
	AND ([ImportDate] = @ImportDate OR @ImportDate IS NULL)
	AND ([TotalAmount] = @TotalAmount OR @TotalAmount IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [RefNo]
	, [ImportDate]
	, [TotalAmount]
	, [Notes]
	, [UpdatedBy]
	, [UpdatedDate]
    FROM
	[dbo].[tblAccessoryImport]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([RefNo] = @RefNo AND @RefNo is not null)
	OR ([ImportDate] = @ImportDate AND @ImportDate is not null)
	OR ([TotalAmount] = @TotalAmount AND @TotalAmount is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_Get_List

AS


				
				SELECT
					[Id],
					[GroupId],
					[Code],
					[Name],
					[Description],
					[MinQuantity],
					[GuaranteePeriod],
					[CreatedBy],
					[CreatedDate],
					[UpdatedBy],
					[UpdateDate]
				FROM
					[dbo].[tblProduct]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblProduct table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblProduct]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[GroupId], O.[Code], O.[Name], O.[Description], O.[MinQuantity], O.[GuaranteePeriod], O.[CreatedBy], O.[CreatedDate], O.[UpdatedBy], O.[UpdateDate]
				FROM
				    [dbo].[tblProduct] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblProduct]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_Insert
(

	@Id int    OUTPUT,

	@GroupId int   ,

	@Code varchar (128)  ,

	@Name nvarchar (256)  ,

	@Description nvarchar (1024)  ,

	@MinQuantity int   ,

	@GuaranteePeriod int   ,

	@CreatedBy varchar (50)  ,

	@CreatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@UpdateDate datetime   
)
AS


				
				INSERT INTO [dbo].[tblProduct]
					(
					[GroupId]
					,[Code]
					,[Name]
					,[Description]
					,[MinQuantity]
					,[GuaranteePeriod]
					,[CreatedBy]
					,[CreatedDate]
					,[UpdatedBy]
					,[UpdateDate]
					)
				VALUES
					(
					@GroupId
					,@Code
					,@Name
					,@Description
					,@MinQuantity
					,@GuaranteePeriod
					,@CreatedBy
					,@CreatedDate
					,@UpdatedBy
					,@UpdateDate
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_Update
(

	@Id int   ,

	@GroupId int   ,

	@Code varchar (128)  ,

	@Name nvarchar (256)  ,

	@Description nvarchar (1024)  ,

	@MinQuantity int   ,

	@GuaranteePeriod int   ,

	@CreatedBy varchar (50)  ,

	@CreatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@UpdateDate datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblProduct]
				SET
					[GroupId] = @GroupId
					,[Code] = @Code
					,[Name] = @Name
					,[Description] = @Description
					,[MinQuantity] = @MinQuantity
					,[GuaranteePeriod] = @GuaranteePeriod
					,[CreatedBy] = @CreatedBy
					,[CreatedDate] = @CreatedDate
					,[UpdatedBy] = @UpdatedBy
					,[UpdateDate] = @UpdateDate
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblProduct] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_GetByGroupId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_GetByGroupId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_GetByGroupId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblProduct table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_GetByGroupId
(

	@GroupId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[GroupId],
					[Code],
					[Name],
					[Description],
					[MinQuantity],
					[GuaranteePeriod],
					[CreatedBy],
					[CreatedDate],
					[UpdatedBy],
					[UpdateDate]
				FROM
					[dbo].[tblProduct]
				WHERE
					[GroupId] = @GroupId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblProduct table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[GroupId],
					[Code],
					[Name],
					[Description],
					[MinQuantity],
					[GuaranteePeriod],
					[CreatedBy],
					[CreatedDate],
					[UpdatedBy],
					[UpdateDate]
				FROM
					[dbo].[tblProduct]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProduct_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProduct_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProduct_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblProduct table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProduct_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@GroupId int   = null ,

	@Code varchar (128)  = null ,

	@Name nvarchar (256)  = null ,

	@Description nvarchar (1024)  = null ,

	@MinQuantity int   = null ,

	@GuaranteePeriod int   = null ,

	@CreatedBy varchar (50)  = null ,

	@CreatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null ,

	@UpdateDate datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [GroupId]
	, [Code]
	, [Name]
	, [Description]
	, [MinQuantity]
	, [GuaranteePeriod]
	, [CreatedBy]
	, [CreatedDate]
	, [UpdatedBy]
	, [UpdateDate]
    FROM
	[dbo].[tblProduct]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([GroupId] = @GroupId OR @GroupId IS NULL)
	AND ([Code] = @Code OR @Code IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
	AND ([MinQuantity] = @MinQuantity OR @MinQuantity IS NULL)
	AND ([GuaranteePeriod] = @GuaranteePeriod OR @GuaranteePeriod IS NULL)
	AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
	AND ([UpdateDate] = @UpdateDate OR @UpdateDate IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [GroupId]
	, [Code]
	, [Name]
	, [Description]
	, [MinQuantity]
	, [GuaranteePeriod]
	, [CreatedBy]
	, [CreatedDate]
	, [UpdatedBy]
	, [UpdateDate]
    FROM
	[dbo].[tblProduct]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([GroupId] = @GroupId AND @GroupId is not null)
	OR ([Code] = @Code AND @Code is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Description] = @Description AND @Description is not null)
	OR ([MinQuantity] = @MinQuantity AND @MinQuantity is not null)
	OR ([GuaranteePeriod] = @GuaranteePeriod AND @GuaranteePeriod is not null)
	OR ([CreatedBy] = @CreatedBy AND @CreatedBy is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	OR ([UpdateDate] = @UpdateDate AND @UpdateDate is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblProductGroup table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_Get_List

AS


				
				SELECT
					[Id],
					[Name],
					[IsInventory]
				FROM
					[dbo].[tblProductGroup]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblProductGroup table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblProductGroup]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Name], O.[IsInventory]
				FROM
				    [dbo].[tblProductGroup] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblProductGroup]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblProductGroup table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (128)  ,

	@IsInventory bit   
)
AS


				
				INSERT INTO [dbo].[tblProductGroup]
					(
					[Name]
					,[IsInventory]
					)
				VALUES
					(
					@Name
					,@IsInventory
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblProductGroup table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_Update
(

	@Id int   ,

	@Name nvarchar (128)  ,

	@IsInventory bit   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblProductGroup]
				SET
					[Name] = @Name
					,[IsInventory] = @IsInventory
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblProductGroup table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblProductGroup] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblProductGroup table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Name],
					[IsInventory]
				FROM
					[dbo].[tblProductGroup]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblProductGroup_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblProductGroup_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblProductGroup_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblProductGroup table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblProductGroup_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (128)  = null ,

	@IsInventory bit   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Name]
	, [IsInventory]
    FROM
	[dbo].[tblProductGroup]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([IsInventory] = @IsInventory OR @IsInventory IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Name]
	, [IsInventory]
    FROM
	[dbo].[tblProductGroup]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([IsInventory] = @IsInventory AND @IsInventory is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblRepository table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_Get_List

AS


				
				SELECT
					[Id],
					[Type],
					[Name],
					[Address],
					[Description],
					[CreatedBy],
					[CreatedDate],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblRepository]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblRepository table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblRepository]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Type], O.[Name], O.[Address], O.[Description], O.[CreatedBy], O.[CreatedDate], O.[UpdatedBy], O.[UpdatedDate]
				FROM
				    [dbo].[tblRepository] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblRepository]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblRepository table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_Insert
(

	@Id int    OUTPUT,

	@Type varchar (1)  ,

	@Name nvarchar (256)  ,

	@Address nvarchar (256)  ,

	@Description nvarchar (1024)  ,

	@CreatedBy varchar (50)  ,

	@CreatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS


				
				INSERT INTO [dbo].[tblRepository]
					(
					[Type]
					,[Name]
					,[Address]
					,[Description]
					,[CreatedBy]
					,[CreatedDate]
					,[UpdatedBy]
					,[UpdatedDate]
					)
				VALUES
					(
					@Type
					,@Name
					,@Address
					,@Description
					,@CreatedBy
					,@CreatedDate
					,@UpdatedBy
					,@UpdatedDate
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblRepository table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_Update
(

	@Id int   ,

	@Type varchar (1)  ,

	@Name nvarchar (256)  ,

	@Address nvarchar (256)  ,

	@Description nvarchar (1024)  ,

	@CreatedBy varchar (50)  ,

	@CreatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@UpdatedDate datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblRepository]
				SET
					[Type] = @Type
					,[Name] = @Name
					,[Address] = @Address
					,[Description] = @Description
					,[CreatedBy] = @CreatedBy
					,[CreatedDate] = @CreatedDate
					,[UpdatedBy] = @UpdatedBy
					,[UpdatedDate] = @UpdatedDate
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblRepository table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblRepository] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblRepository table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Type],
					[Name],
					[Address],
					[Description],
					[CreatedBy],
					[CreatedDate],
					[UpdatedBy],
					[UpdatedDate]
				FROM
					[dbo].[tblRepository]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblRepository_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblRepository_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblRepository_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblRepository table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblRepository_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Type varchar (1)  = null ,

	@Name nvarchar (256)  = null ,

	@Address nvarchar (256)  = null ,

	@Description nvarchar (1024)  = null ,

	@CreatedBy varchar (50)  = null ,

	@CreatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null ,

	@UpdatedDate datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Type]
	, [Name]
	, [Address]
	, [Description]
	, [CreatedBy]
	, [CreatedDate]
	, [UpdatedBy]
	, [UpdatedDate]
    FROM
	[dbo].[tblRepository]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Type] = @Type OR @Type IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Address] = @Address OR @Address IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
	AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Type]
	, [Name]
	, [Address]
	, [Description]
	, [CreatedBy]
	, [CreatedDate]
	, [UpdatedBy]
	, [UpdatedDate]
    FROM
	[dbo].[tblRepository]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Type] = @Type AND @Type is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Address] = @Address AND @Address is not null)
	OR ([Description] = @Description AND @Description is not null)
	OR ([CreatedBy] = @CreatedBy AND @CreatedBy is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblPaymentDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_Get_List

AS


				
				SELECT
					[Id],
					[PaymentId],
					[PurchaseId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPaymentDetail]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblPaymentDetail table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblPaymentDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[PaymentId], O.[PurchaseId], O.[Amount], O.[Date], O.[Notes], O.[UpdatedDate], O.[UpdatedBy]
				FROM
				    [dbo].[tblPaymentDetail] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblPaymentDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPaymentDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_Insert
(

	@Id int    OUTPUT,

	@PaymentId int   ,

	@PurchaseId int   ,

	@Amount int   ,

	@Date datetime   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblPaymentDetail]
					(
					[PaymentId]
					,[PurchaseId]
					,[Amount]
					,[Date]
					,[Notes]
					,[UpdatedDate]
					,[UpdatedBy]
					)
				VALUES
					(
					@PaymentId
					,@PurchaseId
					,@Amount
					,@Date
					,@Notes
					,@UpdatedDate
					,@UpdatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPaymentDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_Update
(

	@Id int   ,

	@PaymentId int   ,

	@PurchaseId int   ,

	@Amount int   ,

	@Date datetime   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblPaymentDetail]
				SET
					[PaymentId] = @PaymentId
					,[PurchaseId] = @PurchaseId
					,[Amount] = @Amount
					,[Date] = @Date
					,[Notes] = @Notes
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPaymentDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblPaymentDetail] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_GetByPaymentId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_GetByPaymentId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_GetByPaymentId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPaymentDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_GetByPaymentId
(

	@PaymentId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[PaymentId],
					[PurchaseId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPaymentDetail]
				WHERE
					[PaymentId] = @PaymentId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_GetByPurchaseId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_GetByPurchaseId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_GetByPurchaseId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPaymentDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_GetByPurchaseId
(

	@PurchaseId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[PaymentId],
					[PurchaseId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPaymentDetail]
				WHERE
					[PurchaseId] = @PurchaseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPaymentDetail table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[PaymentId],
					[PurchaseId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPaymentDetail]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPaymentDetail_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPaymentDetail_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPaymentDetail_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblPaymentDetail table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPaymentDetail_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@PaymentId int   = null ,

	@PurchaseId int   = null ,

	@Amount int   = null ,

	@Date datetime   = null ,

	@Notes nvarchar (1024)  = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [PaymentId]
	, [PurchaseId]
	, [Amount]
	, [Date]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblPaymentDetail]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([PaymentId] = @PaymentId OR @PaymentId IS NULL)
	AND ([PurchaseId] = @PurchaseId OR @PurchaseId IS NULL)
	AND ([Amount] = @Amount OR @Amount IS NULL)
	AND ([Date] = @Date OR @Date IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [PaymentId]
	, [PurchaseId]
	, [Amount]
	, [Date]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblPaymentDetail]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([PaymentId] = @PaymentId AND @PaymentId is not null)
	OR ([PurchaseId] = @PurchaseId AND @PurchaseId is not null)
	OR ([Amount] = @Amount AND @Amount is not null)
	OR ([Date] = @Date AND @Date is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Get_List

AS


				
				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblPurchaseDetail]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblPurchaseDetail table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblPurchaseDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Code], O.[PurchaseId], O.[ProductId], O.[Quantity], O.[Price]
				FROM
				    [dbo].[tblPurchaseDetail] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblPurchaseDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Insert
(

	@Id int    OUTPUT,

	@Code varchar (16)  ,

	@PurchaseId int   ,

	@ProductId int   ,

	@Quantity float   ,

	@Price int   
)
AS


				
				INSERT INTO [dbo].[tblPurchaseDetail]
					(
					[Code]
					,[PurchaseId]
					,[ProductId]
					,[Quantity]
					,[Price]
					)
				VALUES
					(
					@Code
					,@PurchaseId
					,@ProductId
					,@Quantity
					,@Price
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Update
(

	@Id int   ,

	@Code varchar (16)  ,

	@PurchaseId int   ,

	@ProductId int   ,

	@Quantity float   ,

	@Price int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblPurchaseDetail]
				SET
					[Code] = @Code
					,[PurchaseId] = @PurchaseId
					,[ProductId] = @ProductId
					,[Quantity] = @Quantity
					,[Price] = @Price
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblPurchaseDetail] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_GetByProductId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_GetByProductId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_GetByProductId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPurchaseDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_GetByProductId
(

	@ProductId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblPurchaseDetail]
				WHERE
					[ProductId] = @ProductId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_GetByPurchaseId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_GetByPurchaseId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_GetByPurchaseId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPurchaseDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_GetByPurchaseId
(

	@PurchaseId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblPurchaseDetail]
				WHERE
					[PurchaseId] = @PurchaseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPurchaseDetail table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblPurchaseDetail]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPurchaseDetail_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPurchaseDetail_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPurchaseDetail_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblPurchaseDetail table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPurchaseDetail_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Code varchar (16)  = null ,

	@PurchaseId int   = null ,

	@ProductId int   = null ,

	@Quantity float   = null ,

	@Price int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [PurchaseId]
	, [ProductId]
	, [Quantity]
	, [Price]
    FROM
	[dbo].[tblPurchaseDetail]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Code] = @Code OR @Code IS NULL)
	AND ([PurchaseId] = @PurchaseId OR @PurchaseId IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([Quantity] = @Quantity OR @Quantity IS NULL)
	AND ([Price] = @Price OR @Price IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [PurchaseId]
	, [ProductId]
	, [Quantity]
	, [Price]
    FROM
	[dbo].[tblPurchaseDetail]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Code] = @Code AND @Code is not null)
	OR ([PurchaseId] = @PurchaseId AND @PurchaseId is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([Quantity] = @Quantity AND @Quantity is not null)
	OR ([Price] = @Price AND @Price is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblStoredProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_Get_List

AS


				
				SELECT
					[Id],
					[RepositoryId],
					[ProductId],
					[CurrentQuantity]
				FROM
					[dbo].[tblStoredProduct]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblStoredProduct table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblStoredProduct]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[RepositoryId], O.[ProductId], O.[CurrentQuantity]
				FROM
				    [dbo].[tblStoredProduct] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblStoredProduct]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblStoredProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_Insert
(

	@Id int    OUTPUT,

	@RepositoryId int   ,

	@ProductId int   ,

	@CurrentQuantity int   
)
AS


				
				INSERT INTO [dbo].[tblStoredProduct]
					(
					[RepositoryId]
					,[ProductId]
					,[CurrentQuantity]
					)
				VALUES
					(
					@RepositoryId
					,@ProductId
					,@CurrentQuantity
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblStoredProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_Update
(

	@Id int   ,

	@RepositoryId int   ,

	@ProductId int   ,

	@CurrentQuantity int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblStoredProduct]
				SET
					[RepositoryId] = @RepositoryId
					,[ProductId] = @ProductId
					,[CurrentQuantity] = @CurrentQuantity
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblStoredProduct table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblStoredProduct] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_GetByProductId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_GetByProductId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_GetByProductId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblStoredProduct table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_GetByProductId
(

	@ProductId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[RepositoryId],
					[ProductId],
					[CurrentQuantity]
				FROM
					[dbo].[tblStoredProduct]
				WHERE
					[ProductId] = @ProductId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_GetByRepositoryId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_GetByRepositoryId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_GetByRepositoryId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblStoredProduct table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_GetByRepositoryId
(

	@RepositoryId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[RepositoryId],
					[ProductId],
					[CurrentQuantity]
				FROM
					[dbo].[tblStoredProduct]
				WHERE
					[RepositoryId] = @RepositoryId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblStoredProduct table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[RepositoryId],
					[ProductId],
					[CurrentQuantity]
				FROM
					[dbo].[tblStoredProduct]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblStoredProduct_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblStoredProduct_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblStoredProduct_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblStoredProduct table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblStoredProduct_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@RepositoryId int   = null ,

	@ProductId int   = null ,

	@CurrentQuantity int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [ProductId]
	, [CurrentQuantity]
    FROM
	[dbo].[tblStoredProduct]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([RepositoryId] = @RepositoryId OR @RepositoryId IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([CurrentQuantity] = @CurrentQuantity OR @CurrentQuantity IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [ProductId]
	, [CurrentQuantity]
    FROM
	[dbo].[tblStoredProduct]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([RepositoryId] = @RepositoryId AND @RepositoryId is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([CurrentQuantity] = @CurrentQuantity AND @CurrentQuantity is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblUser table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_Get_List

AS


				
				SELECT
					[Username],
					[Password],
					[FullName],
					[Address],
					[Phone],
					[Email],
					[Creator],
					[CreatedDate],
					[Updater],
					[UpdateDate],
					[Status]
				FROM
					[dbo].[tblUser]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblUser table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Username] varchar(50) COLLATE database_default  
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Username])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Username]'
				SET @SQL = @SQL + ' FROM [dbo].[tblUser]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Username], O.[Password], O.[FullName], O.[Address], O.[Phone], O.[Email], O.[Creator], O.[CreatedDate], O.[Updater], O.[UpdateDate], O.[Status]
				FROM
				    [dbo].[tblUser] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Username] = PageIndex.[Username]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblUser]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblUser table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_Insert
(

	@Username varchar (50)  ,

	@Password varchar (50)  ,

	@FullName nvarchar (128)  ,

	@Address nvarchar (256)  ,

	@Phone varchar (20)  ,

	@Email varchar (128)  ,

	@Creator varchar (50)  ,

	@CreatedDate datetime   ,

	@Updater varchar (50)  ,

	@UpdateDate datetime   ,

	@Status char (1)  
)
AS


				
				INSERT INTO [dbo].[tblUser]
					(
					[Username]
					,[Password]
					,[FullName]
					,[Address]
					,[Phone]
					,[Email]
					,[Creator]
					,[CreatedDate]
					,[Updater]
					,[UpdateDate]
					,[Status]
					)
				VALUES
					(
					@Username
					,@Password
					,@FullName
					,@Address
					,@Phone
					,@Email
					,@Creator
					,@CreatedDate
					,@Updater
					,@UpdateDate
					,@Status
					)
				
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblUser table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_Update
(

	@Username varchar (50)  ,

	@OriginalUsername varchar (50)  ,

	@Password varchar (50)  ,

	@FullName nvarchar (128)  ,

	@Address nvarchar (256)  ,

	@Phone varchar (20)  ,

	@Email varchar (128)  ,

	@Creator varchar (50)  ,

	@CreatedDate datetime   ,

	@Updater varchar (50)  ,

	@UpdateDate datetime   ,

	@Status char (1)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblUser]
				SET
					[Username] = @Username
					,[Password] = @Password
					,[FullName] = @FullName
					,[Address] = @Address
					,[Phone] = @Phone
					,[Email] = @Email
					,[Creator] = @Creator
					,[CreatedDate] = @CreatedDate
					,[Updater] = @Updater
					,[UpdateDate] = @UpdateDate
					,[Status] = @Status
				WHERE
[Username] = @OriginalUsername 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblUser table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_Delete
(

	@Username varchar (50)  
)
AS


				DELETE FROM [dbo].[tblUser] WITH (ROWLOCK) 
				WHERE
					[Username] = @Username
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_GetByUsername procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_GetByUsername') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_GetByUsername
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblUser table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_GetByUsername
(

	@Username varchar (50)  
)
AS


				SELECT
					[Username],
					[Password],
					[FullName],
					[Address],
					[Phone],
					[Email],
					[Creator],
					[CreatedDate],
					[Updater],
					[UpdateDate],
					[Status]
				FROM
					[dbo].[tblUser]
				WHERE
					[Username] = @Username
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_GetByFunctionIdFromUserFunction procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_GetByFunctionIdFromUserFunction') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_GetByFunctionIdFromUserFunction
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_GetByFunctionIdFromUserFunction
(

	@FunctionId int   
)
AS


SELECT dbo.[tblUser].[Username]
       ,dbo.[tblUser].[Password]
       ,dbo.[tblUser].[FullName]
       ,dbo.[tblUser].[Address]
       ,dbo.[tblUser].[Phone]
       ,dbo.[tblUser].[Email]
       ,dbo.[tblUser].[Creator]
       ,dbo.[tblUser].[CreatedDate]
       ,dbo.[tblUser].[Updater]
       ,dbo.[tblUser].[UpdateDate]
       ,dbo.[tblUser].[Status]
  FROM dbo.[tblUser]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[tblUserFunction] 
                WHERE dbo.[tblUserFunction].[FunctionId] = @FunctionId
                  AND dbo.[tblUserFunction].[Username] = dbo.[tblUser].[Username]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUser_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUser_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUser_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblUser table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUser_Find
(

	@SearchUsingOR bit   = null ,

	@Username varchar (50)  = null ,

	@Password varchar (50)  = null ,

	@FullName nvarchar (128)  = null ,

	@Address nvarchar (256)  = null ,

	@Phone varchar (20)  = null ,

	@Email varchar (128)  = null ,

	@Creator varchar (50)  = null ,

	@CreatedDate datetime   = null ,

	@Updater varchar (50)  = null ,

	@UpdateDate datetime   = null ,

	@Status char (1)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Username]
	, [Password]
	, [FullName]
	, [Address]
	, [Phone]
	, [Email]
	, [Creator]
	, [CreatedDate]
	, [Updater]
	, [UpdateDate]
	, [Status]
    FROM
	[dbo].[tblUser]
    WHERE 
	 ([Username] = @Username OR @Username IS NULL)
	AND ([Password] = @Password OR @Password IS NULL)
	AND ([FullName] = @FullName OR @FullName IS NULL)
	AND ([Address] = @Address OR @Address IS NULL)
	AND ([Phone] = @Phone OR @Phone IS NULL)
	AND ([Email] = @Email OR @Email IS NULL)
	AND ([Creator] = @Creator OR @Creator IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([Updater] = @Updater OR @Updater IS NULL)
	AND ([UpdateDate] = @UpdateDate OR @UpdateDate IS NULL)
	AND ([Status] = @Status OR @Status IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Username]
	, [Password]
	, [FullName]
	, [Address]
	, [Phone]
	, [Email]
	, [Creator]
	, [CreatedDate]
	, [Updater]
	, [UpdateDate]
	, [Status]
    FROM
	[dbo].[tblUser]
    WHERE 
	 ([Username] = @Username AND @Username is not null)
	OR ([Password] = @Password AND @Password is not null)
	OR ([FullName] = @FullName AND @FullName is not null)
	OR ([Address] = @Address AND @Address is not null)
	OR ([Phone] = @Phone AND @Phone is not null)
	OR ([Email] = @Email AND @Email is not null)
	OR ([Creator] = @Creator AND @Creator is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([Updater] = @Updater AND @Updater is not null)
	OR ([UpdateDate] = @UpdateDate AND @UpdateDate is not null)
	OR ([Status] = @Status AND @Status is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblPayment table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_Get_List

AS


				
				SELECT
					[Id],
					[CustomerId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPayment]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblPayment table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblPayment]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[CustomerId], O.[Amount], O.[Date], O.[Notes], O.[UpdatedDate], O.[UpdatedBy]
				FROM
				    [dbo].[tblPayment] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblPayment]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblPayment table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_Insert
(

	@Id int    OUTPUT,

	@CustomerId int   ,

	@Amount int   ,

	@Date datetime   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblPayment]
					(
					[CustomerId]
					,[Amount]
					,[Date]
					,[Notes]
					,[UpdatedDate]
					,[UpdatedBy]
					)
				VALUES
					(
					@CustomerId
					,@Amount
					,@Date
					,@Notes
					,@UpdatedDate
					,@UpdatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblPayment table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_Update
(

	@Id int   ,

	@CustomerId int   ,

	@Amount int   ,

	@Date datetime   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblPayment]
				SET
					[CustomerId] = @CustomerId
					,[Amount] = @Amount
					,[Date] = @Date
					,[Notes] = @Notes
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblPayment table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblPayment] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_GetByCustomerId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_GetByCustomerId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_GetByCustomerId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPayment table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_GetByCustomerId
(

	@CustomerId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[CustomerId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPayment]
				WHERE
					[CustomerId] = @CustomerId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblPayment table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[CustomerId],
					[Amount],
					[Date],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblPayment]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblPayment_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblPayment_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblPayment_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblPayment table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblPayment_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@CustomerId int   = null ,

	@Amount int   = null ,

	@Date datetime   = null ,

	@Notes nvarchar (1024)  = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [CustomerId]
	, [Amount]
	, [Date]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblPayment]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([CustomerId] = @CustomerId OR @CustomerId IS NULL)
	AND ([Amount] = @Amount OR @Amount IS NULL)
	AND ([Date] = @Date OR @Date IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [CustomerId]
	, [Amount]
	, [Date]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblPayment]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([CustomerId] = @CustomerId AND @CustomerId is not null)
	OR ([Amount] = @Amount AND @Amount is not null)
	OR ([Date] = @Date AND @Date is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblBackupPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_Get_List

AS


				
				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[CustomerId],
					[Date],
					[Total],
					[Commission],
					[RealTotal],
					[Notes]
				FROM
					[dbo].[tblBackupPurchase]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblBackupPurchase table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblBackupPurchase]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[RepositoryId], O.[RefNo], O.[CustomerId], O.[Date], O.[Total], O.[Commission], O.[RealTotal], O.[Notes]
				FROM
				    [dbo].[tblBackupPurchase] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblBackupPurchase]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblBackupPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_Insert
(

	@Id int    OUTPUT,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@CustomerId int   ,

	@Date datetime   ,

	@Total int   ,

	@Commission int   ,

	@RealTotal int   ,

	@Notes nvarchar (1024)  
)
AS


				
				INSERT INTO [dbo].[tblBackupPurchase]
					(
					[RepositoryId]
					,[RefNo]
					,[CustomerId]
					,[Date]
					,[Total]
					,[Commission]
					,[RealTotal]
					,[Notes]
					)
				VALUES
					(
					@RepositoryId
					,@RefNo
					,@CustomerId
					,@Date
					,@Total
					,@Commission
					,@RealTotal
					,@Notes
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblBackupPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_Update
(

	@Id int   ,

	@RepositoryId int   ,

	@RefNo varchar (16)  ,

	@CustomerId int   ,

	@Date datetime   ,

	@Total int   ,

	@Commission int   ,

	@RealTotal int   ,

	@Notes nvarchar (1024)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblBackupPurchase]
				SET
					[RepositoryId] = @RepositoryId
					,[RefNo] = @RefNo
					,[CustomerId] = @CustomerId
					,[Date] = @Date
					,[Total] = @Total
					,[Commission] = @Commission
					,[RealTotal] = @RealTotal
					,[Notes] = @Notes
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblBackupPurchase table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblBackupPurchase] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblBackupPurchase table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[RepositoryId],
					[RefNo],
					[CustomerId],
					[Date],
					[Total],
					[Commission],
					[RealTotal],
					[Notes]
				FROM
					[dbo].[tblBackupPurchase]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchase_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchase_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchase_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblBackupPurchase table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchase_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@RepositoryId int   = null ,

	@RefNo varchar (16)  = null ,

	@CustomerId int   = null ,

	@Date datetime   = null ,

	@Total int   = null ,

	@Commission int   = null ,

	@RealTotal int   = null ,

	@Notes nvarchar (1024)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [RefNo]
	, [CustomerId]
	, [Date]
	, [Total]
	, [Commission]
	, [RealTotal]
	, [Notes]
    FROM
	[dbo].[tblBackupPurchase]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([RepositoryId] = @RepositoryId OR @RepositoryId IS NULL)
	AND ([RefNo] = @RefNo OR @RefNo IS NULL)
	AND ([CustomerId] = @CustomerId OR @CustomerId IS NULL)
	AND ([Date] = @Date OR @Date IS NULL)
	AND ([Total] = @Total OR @Total IS NULL)
	AND ([Commission] = @Commission OR @Commission IS NULL)
	AND ([RealTotal] = @RealTotal OR @RealTotal IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [RepositoryId]
	, [RefNo]
	, [CustomerId]
	, [Date]
	, [Total]
	, [Commission]
	, [RealTotal]
	, [Notes]
    FROM
	[dbo].[tblBackupPurchase]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([RepositoryId] = @RepositoryId AND @RepositoryId is not null)
	OR ([RefNo] = @RefNo AND @RefNo is not null)
	OR ([CustomerId] = @CustomerId AND @CustomerId is not null)
	OR ([Date] = @Date AND @Date is not null)
	OR ([Total] = @Total AND @Total is not null)
	OR ([Commission] = @Commission AND @Commission is not null)
	OR ([RealTotal] = @RealTotal AND @RealTotal is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_Get_List

AS


				
				SELECT
					[Id],
					[ImportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblImportDetail]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblImportDetail table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblImportDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[ImportId], O.[ProductId], O.[Quantity]
				FROM
				    [dbo].[tblImportDetail] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblImportDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_Insert
(

	@Id int    OUTPUT,

	@ImportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS


				
				INSERT INTO [dbo].[tblImportDetail]
					(
					[ImportId]
					,[ProductId]
					,[Quantity]
					)
				VALUES
					(
					@ImportId
					,@ProductId
					,@Quantity
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_Update
(

	@Id int   ,

	@ImportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblImportDetail]
				SET
					[ImportId] = @ImportId
					,[ProductId] = @ProductId
					,[Quantity] = @Quantity
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblImportDetail] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_GetByImportId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_GetByImportId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_GetByImportId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblImportDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_GetByImportId
(

	@ImportId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[ImportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblImportDetail]
				WHERE
					[ImportId] = @ImportId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_GetByProductId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_GetByProductId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_GetByProductId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblImportDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_GetByProductId
(

	@ProductId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[ImportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblImportDetail]
				WHERE
					[ProductId] = @ProductId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblImportDetail table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[ImportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblImportDetail]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblImportDetail_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblImportDetail_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblImportDetail_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblImportDetail table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblImportDetail_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ImportId int   = null ,

	@ProductId int   = null ,

	@Quantity int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [ImportId]
	, [ProductId]
	, [Quantity]
    FROM
	[dbo].[tblImportDetail]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([ImportId] = @ImportId OR @ImportId IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([Quantity] = @Quantity OR @Quantity IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [ImportId]
	, [ProductId]
	, [Quantity]
    FROM
	[dbo].[tblImportDetail]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([ImportId] = @ImportId AND @ImportId is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([Quantity] = @Quantity AND @Quantity is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblAccImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_Get_List

AS


				
				SELECT
					[Id],
					[AccImportId],
					[Name],
					[Quantity],
					[Amount]
				FROM
					[dbo].[tblAccImportDetail]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblAccImportDetail table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblAccImportDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[AccImportId], O.[Name], O.[Quantity], O.[Amount]
				FROM
				    [dbo].[tblAccImportDetail] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblAccImportDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblAccImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_Insert
(

	@Id int    OUTPUT,

	@AccImportId int   ,

	@Name nvarchar (256)  ,

	@Quantity nvarchar (256)  ,

	@Amount int   
)
AS


				
				INSERT INTO [dbo].[tblAccImportDetail]
					(
					[AccImportId]
					,[Name]
					,[Quantity]
					,[Amount]
					)
				VALUES
					(
					@AccImportId
					,@Name
					,@Quantity
					,@Amount
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblAccImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_Update
(

	@Id int   ,

	@AccImportId int   ,

	@Name nvarchar (256)  ,

	@Quantity nvarchar (256)  ,

	@Amount int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblAccImportDetail]
				SET
					[AccImportId] = @AccImportId
					,[Name] = @Name
					,[Quantity] = @Quantity
					,[Amount] = @Amount
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblAccImportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblAccImportDetail] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_GetByAccImportId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_GetByAccImportId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_GetByAccImportId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblAccImportDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_GetByAccImportId
(

	@AccImportId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[AccImportId],
					[Name],
					[Quantity],
					[Amount]
				FROM
					[dbo].[tblAccImportDetail]
				WHERE
					[AccImportId] = @AccImportId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblAccImportDetail table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[AccImportId],
					[Name],
					[Quantity],
					[Amount]
				FROM
					[dbo].[tblAccImportDetail]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblAccImportDetail_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblAccImportDetail_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblAccImportDetail_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblAccImportDetail table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblAccImportDetail_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@AccImportId int   = null ,

	@Name nvarchar (256)  = null ,

	@Quantity nvarchar (256)  = null ,

	@Amount int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [AccImportId]
	, [Name]
	, [Quantity]
	, [Amount]
    FROM
	[dbo].[tblAccImportDetail]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([AccImportId] = @AccImportId OR @AccImportId IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Quantity] = @Quantity OR @Quantity IS NULL)
	AND ([Amount] = @Amount OR @Amount IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [AccImportId]
	, [Name]
	, [Quantity]
	, [Amount]
    FROM
	[dbo].[tblAccImportDetail]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([AccImportId] = @AccImportId AND @AccImportId is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Quantity] = @Quantity AND @Quantity is not null)
	OR ([Amount] = @Amount AND @Amount is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblBackupPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_Get_List

AS


				
				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblBackupPurchaseDetail]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblBackupPurchaseDetail table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblBackupPurchaseDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Code], O.[PurchaseId], O.[ProductId], O.[Quantity], O.[Price]
				FROM
				    [dbo].[tblBackupPurchaseDetail] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblBackupPurchaseDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblBackupPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_Insert
(

	@Id int    OUTPUT,

	@Code varchar (16)  ,

	@PurchaseId int   ,

	@ProductId int   ,

	@Quantity int   ,

	@Price int   
)
AS


				
				INSERT INTO [dbo].[tblBackupPurchaseDetail]
					(
					[Code]
					,[PurchaseId]
					,[ProductId]
					,[Quantity]
					,[Price]
					)
				VALUES
					(
					@Code
					,@PurchaseId
					,@ProductId
					,@Quantity
					,@Price
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblBackupPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_Update
(

	@Id int   ,

	@Code varchar (16)  ,

	@PurchaseId int   ,

	@ProductId int   ,

	@Quantity int   ,

	@Price int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblBackupPurchaseDetail]
				SET
					[Code] = @Code
					,[PurchaseId] = @PurchaseId
					,[ProductId] = @ProductId
					,[Quantity] = @Quantity
					,[Price] = @Price
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblBackupPurchaseDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblBackupPurchaseDetail] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_GetByPurchaseId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_GetByPurchaseId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_GetByPurchaseId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblBackupPurchaseDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_GetByPurchaseId
(

	@PurchaseId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblBackupPurchaseDetail]
				WHERE
					[PurchaseId] = @PurchaseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblBackupPurchaseDetail table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Code],
					[PurchaseId],
					[ProductId],
					[Quantity],
					[Price]
				FROM
					[dbo].[tblBackupPurchaseDetail]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblBackupPurchaseDetail_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblBackupPurchaseDetail_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblBackupPurchaseDetail_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblBackupPurchaseDetail table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblBackupPurchaseDetail_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Code varchar (16)  = null ,

	@PurchaseId int   = null ,

	@ProductId int   = null ,

	@Quantity int   = null ,

	@Price int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [PurchaseId]
	, [ProductId]
	, [Quantity]
	, [Price]
    FROM
	[dbo].[tblBackupPurchaseDetail]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Code] = @Code OR @Code IS NULL)
	AND ([PurchaseId] = @PurchaseId OR @PurchaseId IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([Quantity] = @Quantity OR @Quantity IS NULL)
	AND ([Price] = @Price OR @Price IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [PurchaseId]
	, [ProductId]
	, [Quantity]
	, [Price]
    FROM
	[dbo].[tblBackupPurchaseDetail]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Code] = @Code AND @Code is not null)
	OR ([PurchaseId] = @PurchaseId AND @PurchaseId is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([Quantity] = @Quantity AND @Quantity is not null)
	OR ([Price] = @Price AND @Price is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblCustomer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_Get_List

AS


				
				SELECT
					[Id],
					[CustomerNo],
					[Name],
					[Address],
					[Phone],
					[Fax],
					[Email]
				FROM
					[dbo].[tblCustomer]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblCustomer table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblCustomer]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[CustomerNo], O.[Name], O.[Address], O.[Phone], O.[Fax], O.[Email]
				FROM
				    [dbo].[tblCustomer] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblCustomer]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblCustomer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_Insert
(

	@Id int    OUTPUT,

	@CustomerNo varchar (32)  ,

	@Name nvarchar (128)  ,

	@Address nvarchar (256)  ,

	@Phone varchar (32)  ,

	@Fax varchar (32)  ,

	@Email varchar (32)  
)
AS


				
				INSERT INTO [dbo].[tblCustomer]
					(
					[CustomerNo]
					,[Name]
					,[Address]
					,[Phone]
					,[Fax]
					,[Email]
					)
				VALUES
					(
					@CustomerNo
					,@Name
					,@Address
					,@Phone
					,@Fax
					,@Email
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblCustomer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_Update
(

	@Id int   ,

	@CustomerNo varchar (32)  ,

	@Name nvarchar (128)  ,

	@Address nvarchar (256)  ,

	@Phone varchar (32)  ,

	@Fax varchar (32)  ,

	@Email varchar (32)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblCustomer]
				SET
					[CustomerNo] = @CustomerNo
					,[Name] = @Name
					,[Address] = @Address
					,[Phone] = @Phone
					,[Fax] = @Fax
					,[Email] = @Email
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblCustomer table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblCustomer] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblCustomer table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[CustomerNo],
					[Name],
					[Address],
					[Phone],
					[Fax],
					[Email]
				FROM
					[dbo].[tblCustomer]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblCustomer_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblCustomer_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblCustomer_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblCustomer table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblCustomer_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@CustomerNo varchar (32)  = null ,

	@Name nvarchar (128)  = null ,

	@Address nvarchar (256)  = null ,

	@Phone varchar (32)  = null ,

	@Fax varchar (32)  = null ,

	@Email varchar (32)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [CustomerNo]
	, [Name]
	, [Address]
	, [Phone]
	, [Fax]
	, [Email]
    FROM
	[dbo].[tblCustomer]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([CustomerNo] = @CustomerNo OR @CustomerNo IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Address] = @Address OR @Address IS NULL)
	AND ([Phone] = @Phone OR @Phone IS NULL)
	AND ([Fax] = @Fax OR @Fax IS NULL)
	AND ([Email] = @Email OR @Email IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [CustomerNo]
	, [Name]
	, [Address]
	, [Phone]
	, [Fax]
	, [Email]
    FROM
	[dbo].[tblCustomer]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([CustomerNo] = @CustomerNo AND @CustomerNo is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Address] = @Address AND @Address is not null)
	OR ([Phone] = @Phone AND @Phone is not null)
	OR ([Fax] = @Fax AND @Fax is not null)
	OR ([Email] = @Email AND @Email is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblExport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_Get_List

AS


				
				SELECT
					[Id],
					[RefNo],
					[FromRepositoryId],
					[ToRepositoryId],
					[ExportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblExport]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblExport table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblExport]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[RefNo], O.[FromRepositoryId], O.[ToRepositoryId], O.[ExportDate], O.[TotalAmount], O.[Notes], O.[UpdatedDate], O.[UpdatedBy]
				FROM
				    [dbo].[tblExport] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblExport]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblExport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_Insert
(

	@Id int    OUTPUT,

	@RefNo varchar (16)  ,

	@FromRepositoryId int   ,

	@ToRepositoryId int   ,

	@ExportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblExport]
					(
					[RefNo]
					,[FromRepositoryId]
					,[ToRepositoryId]
					,[ExportDate]
					,[TotalAmount]
					,[Notes]
					,[UpdatedDate]
					,[UpdatedBy]
					)
				VALUES
					(
					@RefNo
					,@FromRepositoryId
					,@ToRepositoryId
					,@ExportDate
					,@TotalAmount
					,@Notes
					,@UpdatedDate
					,@UpdatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblExport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_Update
(

	@Id int   ,

	@RefNo varchar (16)  ,

	@FromRepositoryId int   ,

	@ToRepositoryId int   ,

	@ExportDate datetime   ,

	@TotalAmount int   ,

	@Notes nvarchar (1024)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblExport]
				SET
					[RefNo] = @RefNo
					,[FromRepositoryId] = @FromRepositoryId
					,[ToRepositoryId] = @ToRepositoryId
					,[ExportDate] = @ExportDate
					,[TotalAmount] = @TotalAmount
					,[Notes] = @Notes
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblExport table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblExport] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_GetByToRepositoryId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_GetByToRepositoryId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_GetByToRepositoryId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblExport table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_GetByToRepositoryId
(

	@ToRepositoryId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[RefNo],
					[FromRepositoryId],
					[ToRepositoryId],
					[ExportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblExport]
				WHERE
					[ToRepositoryId] = @ToRepositoryId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_GetByFromRepositoryId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_GetByFromRepositoryId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_GetByFromRepositoryId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblExport table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_GetByFromRepositoryId
(

	@FromRepositoryId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[RefNo],
					[FromRepositoryId],
					[ToRepositoryId],
					[ExportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblExport]
				WHERE
					[FromRepositoryId] = @FromRepositoryId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblExport table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[RefNo],
					[FromRepositoryId],
					[ToRepositoryId],
					[ExportDate],
					[TotalAmount],
					[Notes],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblExport]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExport_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExport_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExport_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblExport table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExport_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@RefNo varchar (16)  = null ,

	@FromRepositoryId int   = null ,

	@ToRepositoryId int   = null ,

	@ExportDate datetime   = null ,

	@TotalAmount int   = null ,

	@Notes nvarchar (1024)  = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [RefNo]
	, [FromRepositoryId]
	, [ToRepositoryId]
	, [ExportDate]
	, [TotalAmount]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblExport]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([RefNo] = @RefNo OR @RefNo IS NULL)
	AND ([FromRepositoryId] = @FromRepositoryId OR @FromRepositoryId IS NULL)
	AND ([ToRepositoryId] = @ToRepositoryId OR @ToRepositoryId IS NULL)
	AND ([ExportDate] = @ExportDate OR @ExportDate IS NULL)
	AND ([TotalAmount] = @TotalAmount OR @TotalAmount IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [RefNo]
	, [FromRepositoryId]
	, [ToRepositoryId]
	, [ExportDate]
	, [TotalAmount]
	, [Notes]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblExport]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([RefNo] = @RefNo AND @RefNo is not null)
	OR ([FromRepositoryId] = @FromRepositoryId AND @FromRepositoryId is not null)
	OR ([ToRepositoryId] = @ToRepositoryId AND @ToRepositoryId is not null)
	OR ([ExportDate] = @ExportDate AND @ExportDate is not null)
	OR ([TotalAmount] = @TotalAmount AND @TotalAmount is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblGuaranteeList table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_Get_List

AS


				
				SELECT
					[Id],
					[ReceivedDate],
					[AttendReturn],
					[ReturnDate],
					[TotalFee],
					[CustomerId],
					[CreatedDate],
					[CreatedBy],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblGuaranteeList]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblGuaranteeList table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblGuaranteeList]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[ReceivedDate], O.[AttendReturn], O.[ReturnDate], O.[TotalFee], O.[CustomerId], O.[CreatedDate], O.[CreatedBy], O.[UpdatedDate], O.[UpdatedBy]
				FROM
				    [dbo].[tblGuaranteeList] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblGuaranteeList]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblGuaranteeList table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_Insert
(

	@Id int    OUTPUT,

	@ReceivedDate datetime   ,

	@AttendReturn datetime   ,

	@ReturnDate datetime   ,

	@TotalFee int   ,

	@CustomerId int   ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblGuaranteeList]
					(
					[ReceivedDate]
					,[AttendReturn]
					,[ReturnDate]
					,[TotalFee]
					,[CustomerId]
					,[CreatedDate]
					,[CreatedBy]
					,[UpdatedDate]
					,[UpdatedBy]
					)
				VALUES
					(
					@ReceivedDate
					,@AttendReturn
					,@ReturnDate
					,@TotalFee
					,@CustomerId
					,@CreatedDate
					,@CreatedBy
					,@UpdatedDate
					,@UpdatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblGuaranteeList table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_Update
(

	@Id int   ,

	@ReceivedDate datetime   ,

	@AttendReturn datetime   ,

	@ReturnDate datetime   ,

	@TotalFee int   ,

	@CustomerId int   ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblGuaranteeList]
				SET
					[ReceivedDate] = @ReceivedDate
					,[AttendReturn] = @AttendReturn
					,[ReturnDate] = @ReturnDate
					,[TotalFee] = @TotalFee
					,[CustomerId] = @CustomerId
					,[CreatedDate] = @CreatedDate
					,[CreatedBy] = @CreatedBy
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblGuaranteeList table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblGuaranteeList] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblGuaranteeList table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[ReceivedDate],
					[AttendReturn],
					[ReturnDate],
					[TotalFee],
					[CustomerId],
					[CreatedDate],
					[CreatedBy],
					[UpdatedDate],
					[UpdatedBy]
				FROM
					[dbo].[tblGuaranteeList]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuaranteeList_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuaranteeList_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuaranteeList_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblGuaranteeList table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuaranteeList_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ReceivedDate datetime   = null ,

	@AttendReturn datetime   = null ,

	@ReturnDate datetime   = null ,

	@TotalFee int   = null ,

	@CustomerId int   = null ,

	@CreatedDate datetime   = null ,

	@CreatedBy varchar (50)  = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [ReceivedDate]
	, [AttendReturn]
	, [ReturnDate]
	, [TotalFee]
	, [CustomerId]
	, [CreatedDate]
	, [CreatedBy]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblGuaranteeList]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([ReceivedDate] = @ReceivedDate OR @ReceivedDate IS NULL)
	AND ([AttendReturn] = @AttendReturn OR @AttendReturn IS NULL)
	AND ([ReturnDate] = @ReturnDate OR @ReturnDate IS NULL)
	AND ([TotalFee] = @TotalFee OR @TotalFee IS NULL)
	AND ([CustomerId] = @CustomerId OR @CustomerId IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [ReceivedDate]
	, [AttendReturn]
	, [ReturnDate]
	, [TotalFee]
	, [CustomerId]
	, [CreatedDate]
	, [CreatedBy]
	, [UpdatedDate]
	, [UpdatedBy]
    FROM
	[dbo].[tblGuaranteeList]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([ReceivedDate] = @ReceivedDate AND @ReceivedDate is not null)
	OR ([AttendReturn] = @AttendReturn AND @AttendReturn is not null)
	OR ([ReturnDate] = @ReturnDate AND @ReturnDate is not null)
	OR ([TotalFee] = @TotalFee AND @TotalFee is not null)
	OR ([CustomerId] = @CustomerId AND @CustomerId is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([CreatedBy] = @CreatedBy AND @CreatedBy is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_Get_List

AS


				
				SELECT
					[Id],
					[ExportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblExportDetail]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblExportDetail table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblExportDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[ExportId], O.[ProductId], O.[Quantity]
				FROM
				    [dbo].[tblExportDetail] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblExportDetail]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_Insert
(

	@Id int    OUTPUT,

	@ExportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS


				
				INSERT INTO [dbo].[tblExportDetail]
					(
					[ExportId]
					,[ProductId]
					,[Quantity]
					)
				VALUES
					(
					@ExportId
					,@ProductId
					,@Quantity
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_Update
(

	@Id int   ,

	@ExportId int   ,

	@ProductId int   ,

	@Quantity int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblExportDetail]
				SET
					[ExportId] = @ExportId
					,[ProductId] = @ProductId
					,[Quantity] = @Quantity
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblExportDetail table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblExportDetail] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_GetByExportId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_GetByExportId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_GetByExportId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblExportDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_GetByExportId
(

	@ExportId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[ExportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblExportDetail]
				WHERE
					[ExportId] = @ExportId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_GetByProductId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_GetByProductId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_GetByProductId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblExportDetail table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_GetByProductId
(

	@ProductId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[ExportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblExportDetail]
				WHERE
					[ProductId] = @ProductId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblExportDetail table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[ExportId],
					[ProductId],
					[Quantity]
				FROM
					[dbo].[tblExportDetail]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblExportDetail_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblExportDetail_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblExportDetail_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblExportDetail table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblExportDetail_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ExportId int   = null ,

	@ProductId int   = null ,

	@Quantity int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [ExportId]
	, [ProductId]
	, [Quantity]
    FROM
	[dbo].[tblExportDetail]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([ExportId] = @ExportId OR @ExportId IS NULL)
	AND ([ProductId] = @ProductId OR @ProductId IS NULL)
	AND ([Quantity] = @Quantity OR @Quantity IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [ExportId]
	, [ProductId]
	, [Quantity]
    FROM
	[dbo].[tblExportDetail]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([ExportId] = @ExportId AND @ExportId is not null)
	OR ([ProductId] = @ProductId AND @ProductId is not null)
	OR ([Quantity] = @Quantity AND @Quantity is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblGuarantee table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_Get_List

AS


				
				SELECT
					[Id],
					[Code],
					[PurchaseDetailId],
					[HistoryPurchaseId],
					[RepositoryId],
					[ReceiveDate],
					[AttendReturn],
					[ReturnDate],
					[ChargeFee],
					[Notes],
					[GuanranteeListId],
					[UpdatedDate],
					[UpdatedBy],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblGuarantee]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblGuarantee table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblGuarantee]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Code], O.[PurchaseDetailId], O.[HistoryPurchaseId], O.[RepositoryId], O.[ReceiveDate], O.[AttendReturn], O.[ReturnDate], O.[ChargeFee], O.[Notes], O.[GuanranteeListId], O.[UpdatedDate], O.[UpdatedBy], O.[CreatedDate], O.[CreatedBy]
				FROM
				    [dbo].[tblGuarantee] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblGuarantee]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblGuarantee table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_Insert
(

	@Id int    OUTPUT,

	@Code varchar (16)  ,

	@PurchaseDetailId int   ,

	@HistoryPurchaseId int   ,

	@RepositoryId int   ,

	@ReceiveDate datetime   ,

	@AttendReturn datetime   ,

	@ReturnDate datetime   ,

	@ChargeFee int   ,

	@Notes nvarchar (1024)  ,

	@GuanranteeListId int   ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  
)
AS


				
				INSERT INTO [dbo].[tblGuarantee]
					(
					[Code]
					,[PurchaseDetailId]
					,[HistoryPurchaseId]
					,[RepositoryId]
					,[ReceiveDate]
					,[AttendReturn]
					,[ReturnDate]
					,[ChargeFee]
					,[Notes]
					,[GuanranteeListId]
					,[UpdatedDate]
					,[UpdatedBy]
					,[CreatedDate]
					,[CreatedBy]
					)
				VALUES
					(
					@Code
					,@PurchaseDetailId
					,@HistoryPurchaseId
					,@RepositoryId
					,@ReceiveDate
					,@AttendReturn
					,@ReturnDate
					,@ChargeFee
					,@Notes
					,@GuanranteeListId
					,@UpdatedDate
					,@UpdatedBy
					,@CreatedDate
					,@CreatedBy
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblGuarantee table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_Update
(

	@Id int   ,

	@Code varchar (16)  ,

	@PurchaseDetailId int   ,

	@HistoryPurchaseId int   ,

	@RepositoryId int   ,

	@ReceiveDate datetime   ,

	@AttendReturn datetime   ,

	@ReturnDate datetime   ,

	@ChargeFee int   ,

	@Notes nvarchar (1024)  ,

	@GuanranteeListId int   ,

	@UpdatedDate datetime   ,

	@UpdatedBy varchar (50)  ,

	@CreatedDate datetime   ,

	@CreatedBy varchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblGuarantee]
				SET
					[Code] = @Code
					,[PurchaseDetailId] = @PurchaseDetailId
					,[HistoryPurchaseId] = @HistoryPurchaseId
					,[RepositoryId] = @RepositoryId
					,[ReceiveDate] = @ReceiveDate
					,[AttendReturn] = @AttendReturn
					,[ReturnDate] = @ReturnDate
					,[ChargeFee] = @ChargeFee
					,[Notes] = @Notes
					,[GuanranteeListId] = @GuanranteeListId
					,[UpdatedDate] = @UpdatedDate
					,[UpdatedBy] = @UpdatedBy
					,[CreatedDate] = @CreatedDate
					,[CreatedBy] = @CreatedBy
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblGuarantee table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblGuarantee] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_GetByGuanranteeListId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_GetByGuanranteeListId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_GetByGuanranteeListId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblGuarantee table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_GetByGuanranteeListId
(

	@GuanranteeListId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseDetailId],
					[HistoryPurchaseId],
					[RepositoryId],
					[ReceiveDate],
					[AttendReturn],
					[ReturnDate],
					[ChargeFee],
					[Notes],
					[GuanranteeListId],
					[UpdatedDate],
					[UpdatedBy],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblGuarantee]
				WHERE
					[GuanranteeListId] = @GuanranteeListId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_GetByHistoryPurchaseId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_GetByHistoryPurchaseId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_GetByHistoryPurchaseId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblGuarantee table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_GetByHistoryPurchaseId
(

	@HistoryPurchaseId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseDetailId],
					[HistoryPurchaseId],
					[RepositoryId],
					[ReceiveDate],
					[AttendReturn],
					[ReturnDate],
					[ChargeFee],
					[Notes],
					[GuanranteeListId],
					[UpdatedDate],
					[UpdatedBy],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblGuarantee]
				WHERE
					[HistoryPurchaseId] = @HistoryPurchaseId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_GetByPurchaseDetailId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_GetByPurchaseDetailId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_GetByPurchaseDetailId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblGuarantee table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_GetByPurchaseDetailId
(

	@PurchaseDetailId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseDetailId],
					[HistoryPurchaseId],
					[RepositoryId],
					[ReceiveDate],
					[AttendReturn],
					[ReturnDate],
					[ChargeFee],
					[Notes],
					[GuanranteeListId],
					[UpdatedDate],
					[UpdatedBy],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblGuarantee]
				WHERE
					[PurchaseDetailId] = @PurchaseDetailId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_GetByRepositoryId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_GetByRepositoryId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_GetByRepositoryId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblGuarantee table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_GetByRepositoryId
(

	@RepositoryId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Id],
					[Code],
					[PurchaseDetailId],
					[HistoryPurchaseId],
					[RepositoryId],
					[ReceiveDate],
					[AttendReturn],
					[ReturnDate],
					[ChargeFee],
					[Notes],
					[GuanranteeListId],
					[UpdatedDate],
					[UpdatedBy],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblGuarantee]
				WHERE
					[RepositoryId] = @RepositoryId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblGuarantee table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Code],
					[PurchaseDetailId],
					[HistoryPurchaseId],
					[RepositoryId],
					[ReceiveDate],
					[AttendReturn],
					[ReturnDate],
					[ChargeFee],
					[Notes],
					[GuanranteeListId],
					[UpdatedDate],
					[UpdatedBy],
					[CreatedDate],
					[CreatedBy]
				FROM
					[dbo].[tblGuarantee]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblGuarantee_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblGuarantee_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblGuarantee_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblGuarantee table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblGuarantee_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Code varchar (16)  = null ,

	@PurchaseDetailId int   = null ,

	@HistoryPurchaseId int   = null ,

	@RepositoryId int   = null ,

	@ReceiveDate datetime   = null ,

	@AttendReturn datetime   = null ,

	@ReturnDate datetime   = null ,

	@ChargeFee int   = null ,

	@Notes nvarchar (1024)  = null ,

	@GuanranteeListId int   = null ,

	@UpdatedDate datetime   = null ,

	@UpdatedBy varchar (50)  = null ,

	@CreatedDate datetime   = null ,

	@CreatedBy varchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [PurchaseDetailId]
	, [HistoryPurchaseId]
	, [RepositoryId]
	, [ReceiveDate]
	, [AttendReturn]
	, [ReturnDate]
	, [ChargeFee]
	, [Notes]
	, [GuanranteeListId]
	, [UpdatedDate]
	, [UpdatedBy]
	, [CreatedDate]
	, [CreatedBy]
    FROM
	[dbo].[tblGuarantee]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Code] = @Code OR @Code IS NULL)
	AND ([PurchaseDetailId] = @PurchaseDetailId OR @PurchaseDetailId IS NULL)
	AND ([HistoryPurchaseId] = @HistoryPurchaseId OR @HistoryPurchaseId IS NULL)
	AND ([RepositoryId] = @RepositoryId OR @RepositoryId IS NULL)
	AND ([ReceiveDate] = @ReceiveDate OR @ReceiveDate IS NULL)
	AND ([AttendReturn] = @AttendReturn OR @AttendReturn IS NULL)
	AND ([ReturnDate] = @ReturnDate OR @ReturnDate IS NULL)
	AND ([ChargeFee] = @ChargeFee OR @ChargeFee IS NULL)
	AND ([Notes] = @Notes OR @Notes IS NULL)
	AND ([GuanranteeListId] = @GuanranteeListId OR @GuanranteeListId IS NULL)
	AND ([UpdatedDate] = @UpdatedDate OR @UpdatedDate IS NULL)
	AND ([UpdatedBy] = @UpdatedBy OR @UpdatedBy IS NULL)
	AND ([CreatedDate] = @CreatedDate OR @CreatedDate IS NULL)
	AND ([CreatedBy] = @CreatedBy OR @CreatedBy IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Code]
	, [PurchaseDetailId]
	, [HistoryPurchaseId]
	, [RepositoryId]
	, [ReceiveDate]
	, [AttendReturn]
	, [ReturnDate]
	, [ChargeFee]
	, [Notes]
	, [GuanranteeListId]
	, [UpdatedDate]
	, [UpdatedBy]
	, [CreatedDate]
	, [CreatedBy]
    FROM
	[dbo].[tblGuarantee]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Code] = @Code AND @Code is not null)
	OR ([PurchaseDetailId] = @PurchaseDetailId AND @PurchaseDetailId is not null)
	OR ([HistoryPurchaseId] = @HistoryPurchaseId AND @HistoryPurchaseId is not null)
	OR ([RepositoryId] = @RepositoryId AND @RepositoryId is not null)
	OR ([ReceiveDate] = @ReceiveDate AND @ReceiveDate is not null)
	OR ([AttendReturn] = @AttendReturn AND @AttendReturn is not null)
	OR ([ReturnDate] = @ReturnDate AND @ReturnDate is not null)
	OR ([ChargeFee] = @ChargeFee AND @ChargeFee is not null)
	OR ([Notes] = @Notes AND @Notes is not null)
	OR ([GuanranteeListId] = @GuanranteeListId AND @GuanranteeListId is not null)
	OR ([UpdatedDate] = @UpdatedDate AND @UpdatedDate is not null)
	OR ([UpdatedBy] = @UpdatedBy AND @UpdatedBy is not null)
	OR ([CreatedDate] = @CreatedDate AND @CreatedDate is not null)
	OR ([CreatedBy] = @CreatedBy AND @CreatedBy is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_Get_List

AS


				
				SELECT
					[Id],
					[Name],
					[Description]
				FROM
					[dbo].[tblFunction]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblFunction table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Id]'
				SET @SQL = @SQL + ' FROM [dbo].[tblFunction]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Id], O.[Name], O.[Description]
				FROM
				    [dbo].[tblFunction] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Id] = PageIndex.[Id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblFunction]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (256)  ,

	@Description nvarchar (1024)  
)
AS


				
				INSERT INTO [dbo].[tblFunction]
					(
					[Name]
					,[Description]
					)
				VALUES
					(
					@Name
					,@Description
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_Update
(

	@Id int   ,

	@Name nvarchar (256)  ,

	@Description nvarchar (1024)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblFunction]
				SET
					[Name] = @Name
					,[Description] = @Description
				WHERE
[Id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[tblFunction] WITH (ROWLOCK) 
				WHERE
					[Id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblFunction table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_GetById
(

	@Id int   
)
AS


				SELECT
					[Id],
					[Name],
					[Description]
				FROM
					[dbo].[tblFunction]
				WHERE
					[Id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_GetByUsernameFromUserFunction procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_GetByUsernameFromUserFunction') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_GetByUsernameFromUserFunction
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_GetByUsernameFromUserFunction
(

	@Username varchar (50)  
)
AS


SELECT dbo.[tblFunction].[Id]
       ,dbo.[tblFunction].[Name]
       ,dbo.[tblFunction].[Description]
  FROM dbo.[tblFunction]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[tblUserFunction] 
                WHERE dbo.[tblUserFunction].[Username] = @Username
                  AND dbo.[tblUserFunction].[FunctionId] = dbo.[tblFunction].[Id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblFunction_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblFunction_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblFunction_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblFunction table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblFunction_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (256)  = null ,

	@Description nvarchar (1024)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Id]
	, [Name]
	, [Description]
    FROM
	[dbo].[tblFunction]
    WHERE 
	 ([Id] = @Id OR @Id IS NULL)
	AND ([Name] = @Name OR @Name IS NULL)
	AND ([Description] = @Description OR @Description IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Id]
	, [Name]
	, [Description]
    FROM
	[dbo].[tblFunction]
    WHERE 
	 ([Id] = @Id AND @Id is not null)
	OR ([Name] = @Name AND @Name is not null)
	OR ([Description] = @Description AND @Description is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets all records from the tblUserFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_Get_List

AS


				
				SELECT
					[Username],
					[FunctionId],
					[Action]
				FROM
					[dbo].[tblUserFunction]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Gets records from the tblUserFunction table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [Username] varchar(50) COLLATE database_default , [FunctionId] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([Username], [FunctionId])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [Username], [FunctionId]'
				SET @SQL = @SQL + ' FROM [dbo].[tblUserFunction]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[Username], O.[FunctionId], O.[Action]
				FROM
				    [dbo].[tblUserFunction] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[Username] = PageIndex.[Username]
					AND O.[FunctionId] = PageIndex.[FunctionId]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[tblUserFunction]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Inserts a record into the tblUserFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_Insert
(

	@Username varchar (50)  ,

	@FunctionId int   ,

	@Action int   
)
AS


				
				INSERT INTO [dbo].[tblUserFunction]
					(
					[Username]
					,[FunctionId]
					,[Action]
					)
				VALUES
					(
					@Username
					,@FunctionId
					,@Action
					)
				
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Updates a record in the tblUserFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_Update
(

	@Username varchar (50)  ,

	@OriginalUsername varchar (50)  ,

	@FunctionId int   ,

	@OriginalFunctionId int   ,

	@Action int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[tblUserFunction]
				SET
					[Username] = @Username
					,[FunctionId] = @FunctionId
					,[Action] = @Action
				WHERE
[Username] = @OriginalUsername 
AND [FunctionId] = @OriginalFunctionId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Deletes a record in the tblUserFunction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_Delete
(

	@Username varchar (50)  ,

	@FunctionId int   
)
AS


				DELETE FROM [dbo].[tblUserFunction] WITH (ROWLOCK) 
				WHERE
					[Username] = @Username
					AND [FunctionId] = @FunctionId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_GetByFunctionId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_GetByFunctionId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_GetByFunctionId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblUserFunction table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_GetByFunctionId
(

	@FunctionId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Username],
					[FunctionId],
					[Action]
				FROM
					[dbo].[tblUserFunction]
				WHERE
					[FunctionId] = @FunctionId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_GetByUsername procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_GetByUsername') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_GetByUsername
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblUserFunction table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_GetByUsername
(

	@Username varchar (50)  
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[Username],
					[FunctionId],
					[Action]
				FROM
					[dbo].[tblUserFunction]
				WHERE
					[Username] = @Username
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_GetByUsernameFunctionId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_GetByUsernameFunctionId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_GetByUsernameFunctionId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Select records from the tblUserFunction table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_GetByUsernameFunctionId
(

	@Username varchar (50)  ,

	@FunctionId int   
)
AS


				SELECT
					[Username],
					[FunctionId],
					[Action]
				FROM
					[dbo].[tblUserFunction]
				WHERE
					[Username] = @Username
					AND [FunctionId] = @FunctionId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.tblUserFunction_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.tblUserFunction_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.tblUserFunction_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By:  ()
-- Purpose: Finds records in the tblUserFunction table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.tblUserFunction_Find
(

	@SearchUsingOR bit   = null ,

	@Username varchar (50)  = null ,

	@FunctionId int   = null ,

	@Action int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [Username]
	, [FunctionId]
	, [Action]
    FROM
	[dbo].[tblUserFunction]
    WHERE 
	 ([Username] = @Username OR @Username IS NULL)
	AND ([FunctionId] = @FunctionId OR @FunctionId IS NULL)
	AND ([Action] = @Action OR @Action IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [Username]
	, [FunctionId]
	, [Action]
    FROM
	[dbo].[tblUserFunction]
    WHERE 
	 ([Username] = @Username AND @Username is not null)
	OR ([FunctionId] = @FunctionId AND @FunctionId is not null)
	OR ([Action] = @Action AND @Action is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

